package excelFileReader;

import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.Format;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

import excelFileReader.ExcelReaderOrdersMitkaplot201301.SheetHandler;

/**
 * @author Idan
 *This class is used to perform ETL on Mitkaplot reports
 */
public class ExcelReaderOrdersMitkaplot201301 
{
	private static Connection conn = null;
	private static ArrayList<Object> row = new ArrayList<Object>();
	private static int i=0;
	public void processOneSheet(String filename) throws Exception {
		OPCPackage pkg = OPCPackage.open(filename);
		XSSFReader r = new XSSFReader( pkg );
		SharedStringsTable sst = r.getSharedStringsTable();

		XMLReader parser = fetchSheetParser(sst);

		// rId2 found by processing the Workbook
		// Seems to either be rId# or rSheet#
		InputStream sheet2 = r.getSheet("rId2");
		InputSource sheetSource = new InputSource(sheet2);
		parser.parse(sheetSource);
		sheet2.close();
	}

	public void processAllSheets(String filename) throws Exception 
	{
		OPCPackage pkg = OPCPackage.open(filename);
		XSSFReader r = new XSSFReader( pkg );
		SharedStringsTable sst = r.getSharedStringsTable();
		
		XMLReader parser = fetchSheetParser(sst);

		Iterator<InputStream> sheets = r.getSheetsData();
		while(sheets.hasNext())
		{
			System.out.println("Processing new sheet:\n");
			InputStream sheet = sheets.next();
			InputSource sheetSource = new InputSource(sheet);
			parser.parse(sheetSource);
			sheet.close();
			System.out.println("");
		}
	}

	public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException 
	{
		XMLReader parser =
			XMLReaderFactory.createXMLReader(
					"org.apache.xerces.parsers.SAXParser"
			);
		ContentHandler handler = new SheetHandler(sst);
		parser.setContentHandler(handler);
		return parser;
	}

	/** 
	 * See org.xml.sax.helpers.DefaultHandler javadocs 
	 */
	public static class SheetHandler extends DefaultHandler
	{
		private SharedStringsTable sst;
		private String lastContents;
		private boolean nextIsString;
		
		private SheetHandler(SharedStringsTable sst) 
		{
			this.sst = sst;
		}
		
		public void startElement(String uri, String localName, String name,
				Attributes attributes) throws SAXException 
				{
			// c => cell
			if(name.equals("c")) {
				// Print the cell reference
				System.out.print(attributes.getValue("r") + " - ");
				
				if ( attributes.getValue("r").startsWith("J") ) // Test if we reached the last column
				{
	
				// Time to dump the line and start a new one
						System.out.print("NEW LINE!\n");

					
				}			
				
				
				// Figure out if the value is an index in the SST
				String cellType = attributes.getValue("t");
				if(cellType != null && cellType.equals("s"))
				{	
					nextIsString = true;
					
				} else {
					nextIsString = false;
				}
			}
			// Clear contents cache
			lastContents = "";
		}
		
		public void endElement(String uri, String localName, String name)
				throws SAXException
				{
			// Process the last contents as required.
			// Do now, as characters() may be called more than once
			if(nextIsString) {
				int idx = Integer.parseInt(lastContents);			
				lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
				
				
            nextIsString = false;
			}

			// v => contents of a cell
			// Output after we've seen the string contents
			if(name.equals("v"))
			{
				row.add(lastContents); // Add value to row
				System.out.println("added " + lastContents);
			
			}
		}

		

		public void characters(char[] ch, int start, int length)
				throws SAXException
				{
			lastContents += new String(ch, start, length);
		}
	}
	

	
	
	public void test() throws SQLException
	{
		Statement stmt = null;
		ResultSet rs = null;
	    stmt = conn.createStatement();
	    rs = stmt.executeQuery("SELECT * FROM status");
	    
	    while(rs.next())
	    {
	    	
	    	System.out.println(rs.getString(1));
	    	System.out.println(rs.getString(2));

	    
	    }
	    
	    if (rs != null) {
	        try {
	            rs.close();
	        } catch (SQLException sqlEx) { } // ignore
	        rs = null;
	    }
	    if (stmt != null) {
	        try {
	            stmt.close();
	        } catch (SQLException sqlEx) { } // ignore
	        stmt = null;
	    }
	}
	
	public static void main(String[] args) throws Exception 
	{
		ExcelReaderOrdersMitkaplot201301 howto = new ExcelReaderOrdersMitkaplot201301();
		
		
		
		try
		{
		    conn = DriverManager.getConnection("jdbc:mysql://localhost/hanitdb?" +
		                                   "user=root&password=&useUnicode=true&characterEncoding=UTF-8");

		   howto.processAllSheets("miktaplotOrders201301.xlsx");

		}
		 catch (SQLException ex)
		 {
			    // handle any errors
			    System.out.println("SQLException: " + ex.getMessage());
			    System.out.println("SQLState: " + ex.getSQLState());
			    System.out.println("VendorError: " + ex.getErrorCode());
			}
		 sendToDB(row); // commit to DB
		
	}

	private static void sendToDB(ArrayList<Object> row) throws SQLException 
	{
		int result;
		Statement stmt = conn.createStatement();
		Statement lastIDstmt = conn.createStatement();
		new ArrayList<Object>();

		
		String repolishSetID = "0";
		String failSetID = "0";
		
		while (i<(row.size()))
		{
			if (row.get(i+5).toString().contains("השמשה") || row.get(i+5).toString().contains("*") || row.get(i+5).toString().equals("0") || row.get(i+5).toString().contains("ליטוש חוזר") || row.get(i+5).toString().length() < 3 || row.get(i+2).toString().contains("NULENS") || row.get(i+2).toString().contains("HBL")) // filter
			{
				System.out.println("Skipping row. " + i/29);
				i=i+29;
			}
			else
			{
					String date = row.get(i+5).toString();
					String dot = ".";
					int indexOfDot = date.indexOf(dot);
					String day = date.substring(0, indexOfDot);
					String month = date.substring(indexOfDot+1);
					if (month.length() >2)
						month = String.valueOf(Integer.parseInt(month.substring(0, 1) +1) );
					
					String completeDate = day + "/" + month + "/" + "2012";
					
					Date formattedDate;
					Timestamp orderDate = null;
					try 
					{
						formattedDate = new SimpleDateFormat("dd/MM/yyyy").parse(completeDate);
						orderDate = new Timestamp(formattedDate.getTime());
					} catch (ParseException e)
					{
						orderDate.setNanos(0);
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					
					
				
		
					
					// Quantity:
				int quantity = Integer.parseInt(row.get(i+9).toString()) + Integer.parseInt(row.get(i+10).toString()) + Integer.parseInt(row.get(i+11).toString()) ;
				
				
					
					
					// filter type
					String type = new String();
					String temp = row.get(i+2).toString().toUpperCase();
					
					switch (temp)
					{
						case "BLENSH":
						{
							type = "BLENS";
							break;
						}
						case "BNYAF":
						{
							type = "BNYAFEZ";
							break;
						}
						case "BNYLENS":
						{
							type = "BUNNYLENS";
							break;
						}
						case "BNYLENSAF":
						{
							type = "BUNNYLENSAF";
							break;
						}
						case "BNYLENSMF":
						{
							type = "BUNNYLENSAF";
							break;
						}
						case "BNYLENSAFM":
						{
							type = "BUNNYLENSAF";
							break;
						}
						case "SLENSAF":
						{
							type = "SLENSAFEZ";
							break;
						}
						case "SLENSAFM":
						{
							type = "SLENSAFEZ";
							break;
						}
						case "SLENSHP":
						{
							type = "SEELENSHP";
							break;
						}
						case "SLENSMF":
						{
							type = "SEELENSMF";
							break;
						}
						default:
						{
							type = temp;
						}
							
	
					}
					
					String orderID = row.get(i+4).toString();
					String diopter = row.get(i+3).toString();

						System.out.println("Commiting: orderID = " + orderID + " type = " + type + " diopter = " + diopter + " quantity = " + quantity + " date = " + completeDate);
						result = stmt.executeUpdate("INSERT IGNORE INTO orders(orderID,type,diopter,orderDate,quantity,statusID) VALUES('" + orderID + "','" + type +"','" + diopter + "','" + orderDate + "','" +quantity + "','99');");
					
					
					
						//result = stmt.executeUpdate("INSERT IGNORE INTO orders(orderID,type,diopter,orderDate,quantity,statusID) VALUES('" + row.get(i+3) + "','" + row.get(i+5) +"','" + row.get(i+6) + "','" + row.get(i+0) + "','0','0');");
					if (result !=1)
						System.out.println("failed!");
					else
						System.out.println("Great success!");	
					
					i=i+29;
			}

		}
		
		stmt.close();
	

	}
	
	/**
	 * This method handles a case of several orderIDs listed in a format such as "XXXX-XXXX"
	 * and breaks it down to individual elements.
	 * 
	 * for example: "12345-12347" or "12345-47"
	 * would be broken down to 12345, 12346 and 12347.
	 */
		
	public static ArrayList<Object> handleOrdersInPolish (Object orders)
	{
		ArrayList<Object> listOfOrdersInPolish = new ArrayList<Object>();
		// remove spaces from the string		
		String str = ((String)orders).replace(" ", "");

		// find the position of "-" in the string
		int index = str.indexOf("-");
				
		// parse the two parts divided by "-" into Integers
		String left = str.substring(0, index-1);
		int leftInt = Integer.parseInt(left);
				
		String right = str.substring(index+1);
		int rightInt = Integer.parseInt(left);
		// if: both parts are Integers, continue.  <-- this is to filter out a case where "-" is in the string but the value is something other than the "XXXX-XXXX" format
		// else: return an empty list.
		
		if ( isNumeric(left) && isNumeric(right) )
		{
			int difference;
			difference = (rightInt - leftInt);
			// if condition: if Right side - Left side < 0 then it's a format similar to 12345-47
			// else: it's a format similar to 12345-12347
			
			if (difference < 0)
			{
				int howMuchToModulu;
				int relevantDigits;
	
				howMuchToModulu = 10 * right.length();
				relevantDigits = leftInt%howMuchToModulu;
				
				difference = rightInt - relevantDigits; // fix the difference to the true positive difference
				
			}
			
			while (difference >=0) 
			{
				listOfOrdersInPolish.add( String.valueOf(leftInt+difference) );
				difference--;
			}
			
		}
		else // Do nothing, listOfOrdersInPolish is empty
		{}
	
		return listOfOrdersInPolish;
		
	}
	
	public static boolean isNumeric(String str)
	{
	  NumberFormat formatter = NumberFormat.getInstance();
	  ParsePosition pos = new ParsePosition(0);
	  formatter.parse(str, pos);
	  return str.length() == pos.getIndex();
	}
	
	public static Calendar handleDate(Object dateFromRow, Object yearFromRow, Calendar finalDate)
	{
		
		System.out.println("ORDERID IS : " + row.get(i+5) + " SteralizeID: " + row.get(i+9) + " Date value is: " + dateFromRow.toString());
		// Break down to day month and year
		int year = (Integer.parseInt(yearFromRow.toString())) + 2000;
		
		// fix possible decimal error from excel
		if (dateFromRow.toString().length() > 4)
		{
			// Excel broke the value in the cell from something like 16.08 to something like 16.07999999999
			BigDecimal tempDecimal = new BigDecimal(dateFromRow.toString());
			tempDecimal = tempDecimal.setScale(2, BigDecimal.ROUND_UP);
			dateFromRow = tempDecimal.toString();
			
		}
		
		int indexOfPoint = (dateFromRow).toString().indexOf(".");
		int day = Integer.parseInt(dateFromRow.toString().substring(0, indexOfPoint));
		
		int month = Integer.parseInt(dateFromRow.toString().substring(indexOfPoint+1, dateFromRow.toString().length())) - 1;
		
		finalDate.set(year, month, day);
		
		return finalDate;	
	}
}
